﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace ShortUrl.Web.Tool
{
    public class ExportExcel1
    {
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="fileName">保存路径</param>
        /// <param name="cellCount">导出excel的列数</param>
        /// <param name="Title">数据表首行-大标题</param>
        /// <returns></returns>
        public static bool ExPortDatas(DataTable dt, string filePath, int cellCount, string Title)
        {
            try
            {
                string[] exp = filePath.Split('.');
                //创建workbook
                IWorkbook workbook = null;

                if (exp[exp.Length - 1] == "xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else if (exp[exp.Length - 1] == "xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                //创建表
                ISheet sheet = workbook.CreateSheet(Title);
                //设置默认高度
                sheet.DefaultColumnWidth = 20;

                //创建标题行
                IRow rowTitle = sheet.CreateRow(0);
                ICell cellTitle = rowTitle.CreateCell(0);
                //设置样式
                ICellStyle cellStyle = workbook.CreateCellStyle();
                //设置边框
                cellStyle.BorderDiagonal = BorderDiagonal.Both;
                //设置对齐样式
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                //设置字体
                IFont font = workbook.CreateFont();
                font.FontName = "微软雅黑";
                font.IsBold = true;
                font.FontHeightInPoints = 18;
                cellStyle.SetFont(font);
                //将样式赋值给标题单元格
                cellTitle.CellStyle = cellStyle;
                //设置合并单元格
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, cellCount));
                cellTitle.SetCellValue(Title);
                //设置列标题
                IRow rowHeader = sheet.CreateRow(1);
                ICellStyle headerStyle = workbook.CreateCellStyle();
                //设置边框
                headerStyle.BorderTop = BorderStyle.Thin;
                headerStyle.BorderBottom = BorderStyle.Thin;
                headerStyle.BorderLeft = BorderStyle.Thin;
                headerStyle.BorderRight = BorderStyle.Thin;
                //设置对齐样式
                headerStyle.Alignment = HorizontalAlignment.Center;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                //设置字体
                IFont fontHeader = workbook.CreateFont();
                fontHeader.FontName = "微软雅黑";
                fontHeader.IsBold = true;
                fontHeader.FontHeightInPoints = 14;
                headerStyle.SetFont(fontHeader);
                //根据表格动态创建头部单元格
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell headerCell = rowHeader.CreateCell(i);
                    headerCell.CellStyle = headerStyle;
                    headerCell.SetCellValue(dt.Columns[i].ColumnName);
                }
                //设置行字体
                IFont rowCellFont = workbook.CreateFont();
                rowCellFont.FontName = "微软雅黑";
                rowCellFont.IsBold = false;
                rowCellFont.FontHeightInPoints = 13;

                //动态创建行
                ICellStyle rowCellStyle = workbook.CreateCellStyle();
                rowCellStyle.BorderTop = BorderStyle.Thin;
                rowCellStyle.BorderBottom = BorderStyle.Thin;
                rowCellStyle.BorderLeft = BorderStyle.Thin;
                rowCellStyle.BorderRight = BorderStyle.Thin;
                rowCellStyle.VerticalAlignment = VerticalAlignment.Center;
                rowCellStyle.Alignment = HorizontalAlignment.Center;
                rowCellStyle.SetFont(rowCellFont);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row2 = sheet.CreateRow(i + 2);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row2.CreateCell(j);
                        cell.CellStyle = rowCellStyle;
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
                //返回数据流
                //写入到客户端  
                File.Delete(filePath);
                using (FileStream ms = new FileStream(filePath, FileMode.CreateNew))
                {
                    workbook.Write(ms);
                    workbook = null;
                }

            }
            catch(Exception ex)
            {
                throw (ex);
            }
            return true;
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="fileName">保存路径</param>
        /// <param name="cellCount">导出excel的列数</param>
        /// <param name="Title">数据表首行-大标题</param>
        /// <returns></returns>
        public static bool ExPortDatasForCaption(DataTable dt, string filePath, int cellCount, string Title)
        {
            try
            {
                string[] exp = filePath.Split('.');
                //创建workbook
                IWorkbook workbook = null;

                if (exp[exp.Length - 1] == "xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else if (exp[exp.Length - 1] == "xlsx")
                {
                    workbook = new XSSFWorkbook();
                }

                //创建表
                ISheet sheet = workbook.CreateSheet(Title);
                //设置默认高度
                sheet.DefaultColumnWidth = 20;

                //创建标题行
                IRow rowTitle = sheet.CreateRow(0);
                ICell cellTitle = rowTitle.CreateCell(0);
                //设置样式
                ICellStyle cellStyle = workbook.CreateCellStyle();
                //设置边框
                cellStyle.BorderDiagonal = BorderDiagonal.Both;
                //设置对齐样式
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                //设置字体
                IFont font = workbook.CreateFont();
                font.FontName = "微软雅黑";
                font.IsBold = true;
                font.FontHeightInPoints = 18;
                cellStyle.SetFont(font);
                //将样式赋值给标题单元格
                cellTitle.CellStyle = cellStyle;
                //设置合并单元格
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, cellCount));
                cellTitle.SetCellValue(Title);
                //设置列标题
                IRow rowHeader = sheet.CreateRow(1);
                ICellStyle headerStyle = workbook.CreateCellStyle();
                //设置边框
                headerStyle.BorderTop = BorderStyle.Thin;
                headerStyle.BorderBottom = BorderStyle.Thin;
                headerStyle.BorderLeft = BorderStyle.Thin;
                headerStyle.BorderRight = BorderStyle.Thin;
                //设置对齐样式
                headerStyle.Alignment = HorizontalAlignment.Center;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                //设置字体
                IFont fontHeader = workbook.CreateFont();
                fontHeader.FontName = "微软雅黑";
                fontHeader.IsBold = true;
                fontHeader.FontHeightInPoints = 14;
                headerStyle.SetFont(fontHeader);
                //根据表格动态创建头部单元格
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell headerCell = rowHeader.CreateCell(i);
                    headerCell.CellStyle = headerStyle;
                    headerCell.SetCellValue(dt.Columns[i].Caption);
                }
                //设置行字体
                IFont rowCellFont = workbook.CreateFont();
                rowCellFont.FontName = "微软雅黑";
                rowCellFont.IsBold = false;
                rowCellFont.FontHeightInPoints = 13;

                //动态创建行
                ICellStyle rowCellStyle = workbook.CreateCellStyle();
                rowCellStyle.BorderTop = BorderStyle.Thin;
                rowCellStyle.BorderBottom = BorderStyle.Thin;
                rowCellStyle.BorderLeft = BorderStyle.Thin;
                rowCellStyle.BorderRight = BorderStyle.Thin;
                rowCellStyle.VerticalAlignment = VerticalAlignment.Center;
                rowCellStyle.Alignment = HorizontalAlignment.Center;
                rowCellStyle.SetFont(rowCellFont);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row2 = sheet.CreateRow(i + 2);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row2.CreateCell(j);
                        cell.CellStyle = rowCellStyle;
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
                //返回数据流
                //写入到客户端  
                using (FileStream ms = new FileStream(filePath, FileMode.CreateNew))
                {
                    workbook.Write(ms);
                    workbook = null;
                }

            }
            catch
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 导出日志数据
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="fileName">保存路径</param>
        /// <param name="cellCount">导出excel的列数</param>
        /// <param name="Title">数据表首行-大标题</param>
        /// <returns></returns>
        public static bool ExportLogData(DataTable dt, string filePath, int cellCount, string Title)
        {
            try
            {
                string[] exp = filePath.Split('.');
                //创建workbook
                IWorkbook workbook = null;

                if (exp[exp.Length - 1] == "xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else if (exp[exp.Length - 1] == "xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                //创建表
                ISheet sheet = workbook.CreateSheet(Title);
                //设置默认宽度
                sheet.DefaultColumnWidth = 20;

                //创建标题行
                IRow rowTitle = sheet.CreateRow(0);
                ICell cellTitle = rowTitle.CreateCell(0);
                //设置样式
                ICellStyle cellStyle = workbook.CreateCellStyle();
                //设置边框
                cellStyle.BorderDiagonal = BorderDiagonal.Both;
                //设置对齐样式
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                //设置字体
                IFont font = workbook.CreateFont();
                font.FontName = "微软雅黑";
                font.IsBold = true;
                font.FontHeightInPoints = 18;
                cellStyle.SetFont(font);
                //将样式赋值给标题单元格
                cellTitle.CellStyle = cellStyle;
                //设置合并单元格
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, cellCount));
                cellTitle.SetCellValue(Title);
                //设置列标题
                IRow rowHeader = sheet.CreateRow(1);
                ICellStyle headerStyle = workbook.CreateCellStyle();
                //设置边框
                headerStyle.BorderTop = BorderStyle.Thin;
                headerStyle.BorderBottom = BorderStyle.Thin;
                headerStyle.BorderLeft = BorderStyle.Thin;
                headerStyle.BorderRight = BorderStyle.Thin;
                //设置对齐样式
                headerStyle.Alignment = HorizontalAlignment.Center;
                headerStyle.VerticalAlignment = VerticalAlignment.Center;
                //设置字体
                IFont fontHeader = workbook.CreateFont();
                fontHeader.FontName = "微软雅黑";
                fontHeader.IsBold = true;
                fontHeader.FontHeightInPoints = 14;
                headerStyle.SetFont(fontHeader);
                //根据表格动态创建头部单元格
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell headerCell = rowHeader.CreateCell(i);
                    headerCell.CellStyle = headerStyle;
                    headerCell.SetCellValue(dt.Columns[i].ColumnName);
                }
                //设置行字体
                IFont rowCellFont = workbook.CreateFont();
                rowCellFont.FontName = "微软雅黑";
                rowCellFont.IsBold = false;
                rowCellFont.FontHeightInPoints = 13;

                //动态创建行
                ICellStyle rowCellStyle = workbook.CreateCellStyle();
                rowCellStyle.BorderTop = BorderStyle.Thin;
                rowCellStyle.BorderBottom = BorderStyle.Thin;
                rowCellStyle.BorderLeft = BorderStyle.Thin;
                rowCellStyle.BorderRight = BorderStyle.Thin;
                rowCellStyle.VerticalAlignment = VerticalAlignment.Center;
                rowCellStyle.Alignment = HorizontalAlignment.Center;
                rowCellStyle.SetFont(rowCellFont);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row2 = sheet.CreateRow(i + 2);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row2.CreateCell(j);
                        cell.CellStyle = rowCellStyle;
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
                //返回数据流
                //写入到客户端  
                File.Delete(filePath);
                using (FileStream ms = new FileStream(filePath, FileMode.CreateNew))
                {
                    workbook.Write(ms);
                    workbook = null;
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            return true;
        }
    }
}
